package com.aier.school.service.impl;


import java.sql.Date;
import java.sql.Timestamp;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import javax.annotation.Resource;

import net.eicp.ghostboy.futil.DateCalculateUtil;

import org.springframework.jdbc.support.GeneratedKeyHolder;
import org.springframework.stereotype.Component;

import com.aier.common.common.Constant;
import com.aier.common.dao.ICommonDAO;
import com.aier.school.common.TeacherLeaveType;
import com.aier.school.entity.School;
import com.aier.school.entity.TeacherIcardInfo;
import com.aier.school.entity.TeacherLeave;
import com.aier.school.entity.mapper.SchoolRowMapper;
import com.aier.school.entity.mapper.TeacherIcardInfoRowMapper;
import com.aier.school.entity.mapper.TeacherLeaveRowMapper;
import com.aier.school.service.ISchoolLeaveService;

@Component("schoolLeaveService")
public class SchoolLeaveServiceImpl_J implements ISchoolLeaveService{

    @Resource(name = "commonDao")
    private ICommonDAO commonDao;

    public void setCommonDao(ICommonDAO commonDao) {
        this.commonDao = commonDao;
    }
    
    @Override
    public Map<String, Object> searchTerLeavesByCondition(int page,int pagesize, int sid,
            List<Long> tids,int type_handle, String sdate, String edate) {
        String sql = " from teacher_leave as tl where tl.tid in (select tid from school_teacher where sid=?) ";
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tl.tid in " + temp_tids;
        }
        if (type_handle!=-1&&type_handle!=0){
            sql += " and tl.type_handle = '" + type_handle+"' ";
        }
        if (sdate!=null&&!sdate.equals("")){
            sql += " and tl.sdate >='" + sdate + "'";
        }
        if (edate!=null&&!edate.equals("")){
            sql += " and tl.edate <='" + edate + "'";
        }
        
        String sql_first = sql;
        sql += " order by tl.edate desc";

        return commonDao
                .queryPage(
                        "select tl.*,(select tname from school_teacher where tid=tl.tid) as teacherName," +
                        "(select account from user where uid=(select uid from school_teacher where tid=tl.tid)) as tAccount," +
                        "(select name from school_grade sc where sc.gid=(select gid from school_teacher where tid=tl.tid)) as typeName," +
                        "(select tname from school_teacher where tid=tl.etid) as euid_name "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { sid }, new Object[] { sid },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHERNAME));    
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherLeave> searchTerLeavesByCondition( int sid,
            List<Long> tids,int type_handle, Integer year_begin, Integer month_begin,
            Integer year_end, Integer month_end){
        String sql = " from teacher_leave as tl where tl.tid in (select tid from school_teacher where sid=?) ";
        if (tids.size()>0){
            String temp_tids = "(";
            for(int i=0;i<tids.size();i++){
                if(i==tids.size()-1){
                    temp_tids = temp_tids + tids.get(i) + ")";
                }else{
                    temp_tids = temp_tids + tids.get(i) + ",";
                }
            }
            sql += " and tl.tid in " + temp_tids;
        }
        if (type_handle!=-1&&type_handle!=0){
            sql += " and tl.type_handle = '" + type_handle+"' ";
        }
        if (null != year_begin){
            sql += " and YEAR(tl.sdate)>=" + year_begin;
        }
        if (null != month_begin){
            sql += " and MONTH(tl.sdate)>=" + month_begin;
        }
        if (null != year_end){
            sql += " and YEAR(tl.edate)<=" + year_end;
        }
        if (null != month_end){
            sql += " and MONTH(tl.edate)<=" + month_end;
        }
        
        sql += " order by tl.sdate desc";

        return (List<TeacherLeave>) commonDao
                .list(
                        "select tl.*,(select tname from school_teacher where tid=tl.tid) as teacherName," +
                        "(select account from user where uid=(select uid from school_teacher where tid=tl.tid)) as tAccount," +
                        "(select name from school_grade sc where sc.gid=(select gid from school_teacher where tid=tl.tid)) as typeName," +
                        "(select tname from school_teacher where tid=tl.etid) as euid_name "
                                + sql,  new Object[] { sid },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHERNAME));   
    }
    
    @Override
    public Map<String, Object> searchTeacherLeavesByCondition(int page,
            int pagesize, int sortable, int tid){
        String sql = " from teacher_leave as tl where tl.tid =?";
        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_SDATE:
                sql += " order by tl.sdate";
                break;
            case TEACHER_SORTABLE_SDATE_DESC:
                sql += " order by tl.sdate desc";
                break;
            case TEACHER_SORTABLE_EDATE:
                sql += " order by tl.edate";
                break;
            case TEACHER_SORTABLE_EDATE_DESC:
                sql += " order by tl.edate desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tl.*,(select realname from user_info where uid=tl.euid) as euid_name  "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { tid }, new Object[] { tid },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHER));    
    }
    
    @Override
    public Map<String, Object> searchTeacherLeavesByException(int page,
            int pagesize, int sortable, int tid,String sdate, String edate){
        String sql = " from teacher_leave as tl where tl.tid =? and tl.type_handle=?";
        
        if (sdate!=null&&!sdate.equals("")){
            sql += " and tl.sdate >='" + sdate + "'";
        }
        if (edate!=null&&!edate.equals("")){
            sql += " and tl.edate <='" + edate + "'";
        }
        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_SDATE:
                sql += " order by tl.sdate";
                break;
            case TEACHER_SORTABLE_SDATE_DESC:
                sql += " order by tl.sdate desc";
                break;
            case TEACHER_SORTABLE_EDATE:
                sql += " order by tl.edate";
                break;
            case TEACHER_SORTABLE_EDATE_DESC:
                sql += " order by tl.edate desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tl.*,(select realname from user_info where uid=tl.euid) as euid_name  "
                                + sql, "select count(*) " + sql_first, page,
                        pagesize, new Object[] { tid,TeacherLeaveType.TYPE_HABDLE_BJ }, new Object[] { tid,TeacherLeaveType.TYPE_HABDLE_BJ },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHER));    
        
    }
    @Override
    public int insertTeacherLeave(Long tid, Timestamp sdate, Timestamp edate,int sid) {
        TeacherLeave tl = new TeacherLeave();
        tl.setTid(tid);
        tl.setSid(sid);
        tl.setSdate(sdate);
        tl.setEdate(edate);
        try {
            commonDao.insert(
                        TeacherLeaveRowMapper.getPreparedStatementCreator(tl),
                        new GeneratedKeyHolder());
            commonDao.update(
                    "update teacher_leave tl set tl.leaveTime=countLeaveTime(?,?,?) where tl.tid=? and tl.sdate=? and tl.edate=? and tl.sid=?",
                    new Object[] {sdate,edate,tid,tid,sdate,edate,sid});
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        return 1;
    }
    
    @Override
    public int insertTeacherLeave(Long tid, Timestamp sdate, Timestamp edate,Integer sid,String desinfo,short state,int type_handle) {
        TeacherLeave tl = new TeacherLeave();
        tl.setTid(tid);
        tl.setSid(sid);
        tl.setSdate(sdate);
        tl.setEdate(edate);
        tl.setState(state);
        tl.setType_handle(type_handle);
        tl.setDesinfo(desinfo);
        try {
            commonDao.insert(
                        TeacherLeaveRowMapper.getPreparedStatementCreator(tl),
                        new GeneratedKeyHolder());
            commonDao.update(
                    "update teacher_leave tl set tl.leaveTime=countLeaveTime(?,?,?) " +
                    "where tl.tid=? and tl.sdate=? and tl.edate=? and tl.sid=? and tl.type_handle=?",
                    new Object[] {sdate,edate,tid,tid,sdate,edate,sid,type_handle});
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        return 1;
    }

    @Override
    public int insertTeacherLeave(Long tid_add, Timestamp sdate, Timestamp edate,Integer sid,String desinfo,short state,int type_handle,long tid) {
        TeacherLeave tl = new TeacherLeave();
        tl.setTid(tid_add);
        tl.setSid(sid);
        tl.setSdate(sdate);
        tl.setEdate(edate);
        tl.setState(state);
        tl.setType_handle(type_handle);
        tl.setDesinfo(desinfo);
        tl.setEtid(tid);
        try {
            commonDao.insert(
                        TeacherLeaveRowMapper.getPreparedStatementCreator(tl),
                        new GeneratedKeyHolder());
            commonDao.update(
                    "update teacher_leave tl set tl.leaveTime=countLeaveTime(?,?,?) " +
                    "where tl.tid=? and tl.sdate=? and tl.edate=? and tl.sid=? and tl.type_handle=?",
                    new Object[] {sdate,edate,tid_add,tid_add,sdate,edate,sid,type_handle});
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }
        return 1;
    }
    @Override
    public int deleteTeacherLeave(Long lid) {
        if (commonDao.update("delete from teacher_leave where lid=?",
                new Object[] { lid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }
    
    /**
     * 判断用户是否有操作该学校权限
     * @param uid
     * @param sid
     * @return
     */
    private boolean checkUserSchool(Long uid,Integer sid){
        return (commonDao.countByQuali("select * from school_manager where uid=? and sid=?",new Object[]{uid,sid})<=0)?false:true;
    }
    
    /**
     * 更新考勤表
     */
    @Override
    public int updateTeacherAttendanceMonth(Long tid, Timestamp sdate, Timestamp edate) {
        int s = 0;
        
        Date sDateYMD = Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(sdate));
        Date eDateYMD = Date.valueOf(new SimpleDateFormat("yyyy-MM-dd").format(edate));
        // 请假开始时间和结束时间是同一天
        if (sDateYMD.equals(eDateYMD)) {
            s = updateTeacherAM(tid, sdate.toString(), edate.toString());
            return s;
        } 
        
        // 请假开始时间和结束时间不是同一天
        // 判断请假的天数
        int dayCount = (int) ((eDateYMD.getTime() - sDateYMD.getTime())/Constant.num_1000/Constant.num_3600/Constant.num_24);
        
        String sdateStr = sdate.toString();
        String edateStr;
        for (int i=0; i <= dayCount; i++) {
            if (sDateYMD.equals(eDateYMD)) {
                edateStr = edate.toString();
            } else {
                edateStr = sDateYMD+" 23:59:59";
            }
          
            s = updateTeacherAM(tid, sdateStr, edateStr);
           
            sDateYMD = new java.sql.Date(DateCalculateUtil.getDateAfter(sDateYMD, 1).getTime());
            sdateStr = sDateYMD.toString() + " 00:00:00";
           
        }
        return s;
        
    }
    
    public int updateTeacherAM(Long tid, String sdate, String edate) {
        // 查看该天是否有打卡记录
        String tsStr = sdate.substring(0, Constant.num_10);
        @SuppressWarnings("unchecked")
        List<TeacherIcardInfo> tii = (List<TeacherIcardInfo>) commonDao.list(
               "select * FROM teacher_icard_info t WHERE t.tid = ? AND t.cdate>=? AND t.cdate<= ?"
               ,new Object[] {tid,tsStr+" 00:00:00",tsStr+" 23:59:59"},new TeacherIcardInfoRowMapper());
        if (null != tii && tii.size() > 0) {
            commonDao
                .update("update teacher_attendance_month set accumulation=accumulation-countLeaveTime(?,?,?)*60," +
                        "`leave`=`leave`+countLeaveTime(?,?,?) where tid = ?",
                        new Object[] {sdate,edate,tid,sdate,edate,tid,tid });
           
        } else {
            commonDao
                 .update("update teacher_attendance_month set absenteeism=absenteeism-1," +
                         "accumulation=accumulation-countLeaveTime(?,?,?)*60,`leave`=`leave`+countLeaveTime(?,?,?) where tid = ?",
                        new Object[] {sdate,edate,tid,sdate,edate,tid,tid });
           
        }
        return 1;
    }

    @Override
    public School searchSchoolByTid(int tid) {
        return (School) commonDao.listByObject("select * from school where sid=(select sid from teacher_school where tid=?)", 
                new Object[] {tid }, new SchoolRowMapper());
    }

    @Override
    public int updateTeacherLeave(long lid, short state_leave,long tid,String desinfo_do) {
        if (commonDao.update("update teacher_leave set state=?,etid=?,desinfo_do=? where lid=?",
                new Object[] { state_leave,tid,desinfo_do,lid }) > 0) {
            return 1;
        } else {
            return 0;
        }
    }

    @Override
    public int insertTeacherLeaves(List<Long> tids, Timestamp sdate,
            Timestamp edate, int sid, String desinfo, short state,
             Integer type_handle,long utid) {
        try {
            for(Long tid:tids){
                TeacherLeave tl = new TeacherLeave();
                tl.setTid(tid);
                tl.setSid(sid);
                tl.setSdate(sdate);
                tl.setEdate(edate);
                tl.setState(state);
                tl.setType_handle(type_handle);
                tl.setDesinfo(desinfo);
                tl.setEtid(utid);
                commonDao.insert(
                        TeacherLeaveRowMapper.getPreparedStatementCreator(tl),
                                new GeneratedKeyHolder());
                commonDao.update(
                        "update teacher_leave tl set tl.leaveTime=countLeaveTime(?,?,?) " +
                        "where tl.tid=? and tl.sdate=? and tl.edate=? and tl.sid=? and tl.type_handle=?",
                        new Object[] {sdate,edate,tid,tid,sdate,edate,sid,type_handle});            
            }
        } catch (Exception e) {
            e.printStackTrace();
            return 0;
        }

        return 1;
    }

    @Override
    public Map<String, Object> searchTerLeaves(int page,
            int pagesize, int sortable, int sid, Long uid,
            Integer taid) {
        if(!checkUserSchool(uid, sid)){
            return null;
        }
        String sql = " from teacher_leave as tl where tl.tid=(select tid from teacher_attendance_month where taid=?) and " +
                "YEAR(tl.sdate)=(select `year` from teacher_attendance_month where taid=?) and MONTH(tl.sdate)=(select `month` " +
                "from teacher_attendance_month where taid=?) and tl.type_handle=?";
    
        String sql_first = sql;
        switch (sortable) {
            case TEACHER_SORTABLE_NAME:
                sql += " order by teacherName";
                break;
            case TEACHER_SORTABLE_NAME_DESC:
                sql += " order by teacherName desc";
                break;
            case TEACHER_SORTABLE_SDATE:
                sql += " order by tl.sdate";
                break;
            case TEACHER_SORTABLE_SDATE_DESC:
                sql += " order by tl.sdate desc";
                break;
            case TEACHER_SORTABLE_EDATE:
                sql += " order by tl.edate";
                break;
            case TEACHER_SORTABLE_EDATE_DESC:
                sql += " order by tl.edate desc";
                break;
        }
        return commonDao
                .queryPage(
                        "select tl.*,(select name from teacher where tid=tl.tid) as teacherName," +
                        "(select account from user where uid=(select uid from teacher where tid=tl.tid)) as tAccount," +
                        "(select name from school_grade sc where sc.gid=(select gid from teacher where tid=tl.tid)) as typeName," +
                        "(select realname from user_info where uid=tl.euid) as euid_name "
                        + sql, "select count(*) " + sql_first, page,pagesize, new Object[] { taid,taid,taid,TeacherLeaveType.TYPE_HABDLE_BJ }, 
                        new Object[] { taid,taid,taid,TeacherLeaveType.TYPE_HABDLE_BJ },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHERNAME));    
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherLeave> searchTeacherLeavesByCondition(int tid) {
        String sql = " from teacher_leave as tl where tl.tid =?";
        sql += " order by tl.edate desc";
        return (List<TeacherLeave>) commonDao.list(
                        "select tl.*,(select realname from user_info where uid=tl.euid) as euid_name  "
                                + sql, new Object[] { tid },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHER));    
    }

    @SuppressWarnings("unchecked")
    @Override
    public List<TeacherLeave> searchSchoolTeacherLeaves(long sid,Short state_leave) {
        String sql = " from teacher_leave as tl where tl.tid in (select tid from teacher_school where sid=?) ";
        if(state_leave!=null && state_leave!=0){
            if(state_leave==1){
                sql += " and tl.state=1 ";
            }else{
                sql += " and tl.state!=1 ";
            }
        }
        sql += " order by tl.edate desc";
        return (List<TeacherLeave>) commonDao.list(
                        "select tl.*,(select realname from user_info where uid=tl.euid) as euid_name  "
                                + sql, new Object[] { sid },
                        new TeacherLeaveRowMapper(TeacherLeaveRowMapper.AND_TEACHER));    
    }
}
